/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cl.darksigns.webstat.back.dao;

import cl.darksigns.webstat.back.vo.RequestVO;
import cl.darksigns.webstat.back.vo.HeaderVO;
import cl.darksigns.webstat.back.vo.SessionVO;
import cl.darksigns.webstat.back.vo.DataVO;
import cl.darksigns.webstat.back.exception.WebStatException;
import cl.darksigns.webstat.back.vo.WebStatSpecificVO;
import cl.darksigns.webstat.back.vo.WebStatVO;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ResourceBundle;
import java.util.logging.Level;

/**
 *
 * @author _ViKtOr
 */
public class WebStatDao {

    String databaseURL = "jdbc:firebirdsql:local:d:/webstat_db.fdb";
    String user = "sysdba";
    String password = "masterkey";
    String driverName = "org.firebirdsql.jdbc.FBDriver";
    Connection connection = null;
    Statement st = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    Logger logger = Logger.getLogger(WebStatDao.class);
    ResourceBundle bundle = ResourceBundle.getBundle(
            System.getProperty("WEBSTATENVIRONMENT") + "_webstat_sql");

    /**
     * This method establish the connection with database
     * @return
     * @throws cl.darksigns.webstat.back.exception.WebStatException
     */
    private Connection getConnection() throws WebStatException {
        Connection conn;
        try {
            logger.debug("Instantiating driver");
            Class.forName("org.firebirdsql.jdbc.FBDriver");
            logger.debug("Getting connection");
            conn = java.sql.DriverManager.getConnection(databaseURL, user, password);
            conn.setAutoCommit(false);
            logger.debug("Returning connection");
        } catch (ClassNotFoundException ex) {
            logger.error("Error registering driver: ", ex);
            throw new WebStatException("Technical Error, unable to register connection driver");
        } catch (SQLException ex) {
            logger.error("Error creating connection: ", ex);
            throw new WebStatException("Technical Error, unable to connect with database");
        }
        return conn;
    }

    public int getNextId() throws WebStatException {
        int ret = 0;
        try {
            connection = getConnection();
            String sql = bundle.getString("SQL_NEXT_ID");
            logger.debug("sql: " + sql);
            st = connection.createStatement();
            rs = st.executeQuery(sql);
            rs.next();
            ret = rs.getInt("next");
            logger.info("Next id: " + ret);
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(WebStatDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
    }

    /**
     *  
     * @param vo
     * @throws cl.darksigns.webstat.back.exception.WebStatException
     */
    public void insertRequest(int reqId, RequestVO vo) throws WebStatException {
        try {
            connection = getConnection();
            String sql = bundle.getString("SQL_REQUEST_INSERT");
            logger.debug("sql=" + sql);
            logger.debug("Setting parameters");
            pst = connection.prepareStatement(sql);
            logger.info("req id: " + reqId);
            pst.setInt(1, reqId);
            logger.info("session id: " + vo.getReqSessionId());
            pst.setString(2, vo.getReqSessionId());
            logger.info("req app id: " + reqId);
            pst.setInt(3, vo.getReqAppId());
            logger.info("req target: " + vo.getReqTargetUri());
            pst.setString(4, vo.getReqTargetUri());
            logger.info("req source: " + vo.getReqSourceUri());
            pst.setString(5, vo.getReqSourceUri());
            logger.info("req type: " + vo.getReqType());
            pst.setString(6, vo.getReqType());
            logger.info("req user id: " + vo.getReqUserId());
            pst.setString(7, vo.getReqUserId());
            logger.info("req event id: " + vo.getReqEventId());
            pst.setString(8, vo.getReqEventId());
            logger.info("Executing statement");
            int ret = pst.executeUpdate();
            logger.info("Statement executed: " + ret);
            connection.commit();
        } catch (SQLException ex) {
            logger.error("Error: ", ex);
            ex.printStackTrace();
            throw new WebStatException("Error técnico");
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
                logger.error(e);
                throw new WebStatException("Error closing connection");
            }
        }
    }

    /**
     *
     * @param reqId
     * @param header
     * @throws WebStatException
     */
    public void insertHeader(int reqId, HeaderVO header) throws WebStatException {
        connection = getConnection();
        String sql = bundle.getString("SQL_HEADER_INSERT");
        try {
            pst = connection.prepareStatement(sql);
            pst.setInt(1, reqId);
            pst.setInt(2, header.getReqHeadId());
            pst.setString(3, header.getReqHeadName());
            pst.setString(4, header.getReqHeadStringValue());
            int ret = pst.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e);
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
                logger.error(e);
                throw new WebStatException("Error closing connection");
            }
        }
    }

    /**
     *
     * @param reqId
     * @param session
     * @throws WebStatException
     */
    public void insertSession(int reqId, SessionVO session) throws WebStatException {
        connection = getConnection();
        String sql = bundle.getString("SQL_SESSION_INSERT");
        try {
            pst = connection.prepareStatement(sql);
            pst.setInt(1, reqId);
            logger.debug("req sess id: " + session.getReqSessId());
            pst.setInt(2, session.getReqSessId());
            logger.debug("sess name: " + session.getReqSessName());
            pst.setString(3, session.getReqSessName());
            logger.debug("sess value: " + session.getReqSessStringValue());
            pst.setString(4, session.getReqSessStringValue());
            int ret = pst.executeUpdate();
            logger.info("Finish inserting session: " + ret);
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e);

        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
                logger.error(e);
                throw new WebStatException("Error closing connection");
            }
        }
    }

    public void insertData(int reqId, DataVO data) throws WebStatException {
        connection = getConnection();
        String sql = bundle.getString("SQL_DATA_INSERT");
        try {
            pst = connection.prepareStatement(sql);
            pst.setInt(1, reqId);
            logger.debug("req sess id: " + data.getReqDataId());
            pst.setInt(2, data.getReqDataId());
            logger.debug("sess name: " + data.getReqDataName());
            pst.setString(3, data.getReqDataName());
            logger.debug("sess value: " + data.getReqDataStringValue());
            pst.setString(4, data.getReqDataStringValue());
            int ret = pst.executeUpdate();
            logger.info("Finish inserting data: " + ret);
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e);

        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
                logger.error(e);
                throw new WebStatException("Error closing connection");
            }
        }
    }

    public boolean  validateUser(String userName, String userPwd) throws WebStatException {
        boolean ret = false;
        try {
            connection = getConnection();
            String sql = bundle.getString("SQL_VALIDATE_USER");
            logger.info("sql: " + sql);
            pst = connection.prepareStatement(sql);
            pst.setString(1, userName);
            pst.setString(2, userPwd);
            ResultSet res = pst.executeQuery();
            res.next();
            int count = res.getInt("CONT");
            if( count == 1){
                ret = true;
            }
            
        } catch (SQLException ex) {
            ex.printStackTrace();
            logger.error(ex);
        }finally{
            try {
                connection.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
                logger.error(ex);
                throw new WebStatException("Error closing connection");
            }
        }
        return ret;
    }
    public void insertSpecificInformation(WebStatSpecificVO wst) throws WebStatException{
        
    }
    public WebStatVO[] getStandardReportFiles(String startDate, String endDate){
        return null;
    }
    public WebStatSpecificVO[] getSpecificReport(String startDate, String endDate){
        return null;
    }
    
}
